<%@page language="java" contentType="text/javascript; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.DecimalFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%
	
	String mapsearchlat = request.getParameter("mapsearchlat");
	String mapsearchlng = request.getParameter("mapsearchlng");
	
	String p = request.getParameter("pages"); 
    String rpp = request.getParameter("rpp");
    String start = request.getParameter("start");
    int rpps = Integer.parseInt(rpp);
    int starts = Integer.parseInt(start);
	
	Connection conn = null;
	PreparedStatement pstmt = null;
    
	JSONObject jo = new JSONObject();
	JSONObject jo2 = new JSONObject();
    JSONArray ja = new JSONArray();
    
    
    try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		
		
		String select = "select Number, ID, Host, Title, Theme, Lat, Lng, Content, StartDate, StartTime, Date, Time, ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance from Ting WHERE Startdate > CURRENT_DATE( ) - INTERVAL 2 DAY HAVING distance < 20 ORDER BY distance limit ?, ?;";
		pstmt = conn.prepareStatement(select);
		pstmt.setString(1, mapsearchlat);
		pstmt.setString(2, mapsearchlng);
		pstmt.setString(3, mapsearchlat);
		pstmt.setInt(4, starts);
		pstmt.setInt(5, rpps);
		
		ResultSet rs = pstmt.executeQuery();
		
		
		while(rs.next()){
			String num = rs.getString("Number");
			String id = rs.getString("ID");
			String host = rs.getString("Host");
			String title = rs.getString("Title");
			String theme = rs.getString("Theme");
			String lat = rs.getString("Lat");
			String lng = rs.getString("Lng");
			String content = rs.getString("Content");
			if (title != null) {
				title = title.replace("\n","<br>");
				title = title.replace("\u0020","&nbsp;");
			}
			if (content != null) {
				content = content.replace("\n","<br>");
				content = content.replace("\u0020","&nbsp;");
			}
			String startdate = rs.getString("StartDate");
			String starttime = rs.getString("StartTime");
			String hhmm = starttime.substring(0, 5);
			String date = rs.getString("Date");
			String time = rs.getString("Time");
			
			
			jo.put("NUMBER", num);
			jo.put("ID", id);
			jo.put("HOST", host);
			jo.put("TITLE", title);
			jo.put("THEME", theme);
			jo.put("LAT", lat);
			jo.put("LNG", lng);
			jo.put("CONTENT", content);
			jo.put("STARTDATE", startdate);
			jo.put("STARTTIME", hhmm);
			jo.put("DATE", date);
			jo.put("TIME", time);
			ja.add(jo);
			jo2.put("results", ja);
		}
		
		String select2 = "select Number, ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance from Ting WHERE Startdate > CURRENT_DATE( ) - INTERVAL 2 DAY HAVING distance < 20 ORDER BY distance;"; 
		pstmt = conn.prepareStatement(select2);
		pstmt.setString(1, mapsearchlat);
		pstmt.setString(2, mapsearchlng);
		pstmt.setString(3, mapsearchlat);
		
		ResultSet rs2 = pstmt.executeQuery();
		
		rs2.last();
		int total = rs2.getRow();
		rs2.beforeFirst();
		
		jo2.put("total", new Integer(total));
		out.print(jo2);
    }
    
	finally {
		try { 
            pstmt.close();
        } 
        catch (Exception ignored) {
        }
        try { 
            conn.close();
        } 
        catch (Exception ignored) {
        }
    }
%>);